package com.softer.chengxin.project.dao.provider;

import com.softer.chengxin.common.domin.Page;
import com.softer.chengxin.common.enums.NumberEnum;
import org.apache.commons.lang3.StringUtils;

import java.util.HashMap;

/**
 * @author wangxinrui
 * @date 2018/10/12
 */
public class ProManagerDaoProvider {

    /**
     * 加载企业信息
     */
    public String load(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String qyName = (String) map.get("qyName");
        String projectName = (String) map.get("projectName");
        String state = (String) map.get("state");
        String area = (String) map.get("area");
        Page page = (Page) map.get("page");
        String district = (String) map.get("district");
        String street = (String) map.get("street");
        String cxBeginDate = (String) map.get("cxBeginDate");
        String cxEndDate = (String) map.get("cxEndDate");

        sql.append("select   c.`name` as qyName,p.id as id,DATE_FORMAT(p.gmt_modified, '%Y-%m-%d') as gmtCreate,p.project_name as projectName,concat(IFNULL(t.`name`,''),IFNULL(d.`name`,''),IFNULL(ts.`name`,'')) AS detailedAddress,p.project_address as projectAddress,p.state as state,DATE_FORMAT(p.entrust_begin_time, '%Y-%m-%d') AS `entrustBeginTime` ");
        sql.append(" from company_project p left join company_base_info c on p.company_id=c.id ");
        sql.append(" LEFT JOIN district_street t ON t.id = p.district LEFT JOIN district_street d ON d.id = p.street");
        sql.append(" LEFT JOIN district_street ts ON ts.id = p.community ");
        sql.append(" where p.state in(1,2,3,4,5,6)");

        if (StringUtils.isNotBlank(area)) {
            sql.append(" and (CASE p.community WHEN 0 THEN p.street in (").append(area).append(") ELSE p.community in (").append(area).append(") end)");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%','").append(qyName).append("','%')");
        }
        if (StringUtils.isNotBlank(projectName)) {
            sql.append(" and p.project_name like CONCAT('%',#{projectName},'%')");
        }
        if (StringUtils.isNotBlank(state)) {
            sql.append(" and p.state in (").append(state).append(")");
        }
        if (StringUtils.isNotBlank(district)) {
            sql.append(" and p.district = #{district}");
        }
        if (StringUtils.isNotBlank(street)) {
            sql.append(" and p.street = #{street}");
        }
        if (StringUtils.isNotBlank(cxBeginDate)) {
            sql.append(" and p.gmt_create>=#{cxBeginDate}");
        }
        if (StringUtils.isNotBlank(cxEndDate)) {
            sql.append(" and p.gmt_create<=#{cxEndDate}");
        }
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 加载企业信息 条数
     */
    public String loadCount(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        String qyName = (String) map.get("qyName");
        String projectName = (String) map.get("projectName");
        String state = (String) map.get("state");
        String district = (String) map.get("district");
        String street = (String) map.get("street");
        String cxBeginDate = (String) map.get("cxBeginDate");
        String cxEndDate = (String) map.get("cxEndDate");

        sql.append("select count(*) from company_project p left join company_base_info c on p.company_id=c.id  ");
        sql.append(" LEFT JOIN district_street t ON t.id = p.district LEFT JOIN district_street d ON d.id = p.street");
        sql.append(" where p.state in(1,2,3,4,5,6)");
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and (CASE p.community WHEN 0 THEN p.street in (").append(area).append(") ELSE p.community in (").append(area).append(") end)");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%','").append(qyName).append("','%')");
        }
        if (StringUtils.isNotBlank(projectName)) {
            sql.append(" and p.project_name like CONCAT('%',#{projectName},'%')");
        }
        if (StringUtils.isNotBlank(state)) {
            sql.append(" and p.state in (").append(state).append(")");
        }
        if (StringUtils.isNotBlank(district)) {
            sql.append(" and p.district =#{district}");
        }
        if (StringUtils.isNotBlank(street)) {
            sql.append(" and p.street =#{street}");
        }
        if (StringUtils.isNotBlank(cxBeginDate)) {
            sql.append(" and p.gmt_create>=#{cxBeginDate}");
        }
        if (StringUtils.isNotBlank(cxEndDate)) {
            sql.append(" and p.gmt_create<=#{cxEndDate}");
        }
        return sql.toString();
    }


    /**
     * 查询企业项目待审批信息
     */
    public String findByCondition(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        Page page = (Page) map.get("page");
        String area = (String) map.get("area");
        String qyName = (String) map.get("qyName");
        String proName = (String) map.get("proName");
        Integer state = (Integer) map.get("state");

        sql.append("select @rowno:=@rowno+1 row,c.`name` as qyName,p.id as id,DATE_FORMAT(p.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(p.gmt_modified, '%Y-%m-%d') as gmtModified,p.project_id as projectId,p.project_name as projectName,p.company_id as companyId,p.property_type AS propertyTypeCode,(SELECT GROUP_CONCAT(name) FROM dic_info WHERE FIND_IN_SET(CODE,p.property_type)) AS propertyType,p.province as province,p.city as city,p.district as district,p.street as street,p.community，p.project_address as projectAddress,p.project_develop as projectDevelop,p.with_elevator_area as withElevatorArea,p.public_facilities_area as publicFacilitiesArea,p.housing_quantity as housingQuantity,p.non_residential_quantity as nonResidentialQuantity,p.number_residents as numberResidents,p.elevators as elevators,p.committee_time as committeeTime,p.committee_director as committeeDirector,p.east_end as eastEnd,p.west_end as westEnd,p.south_end as southEnd,p.north_end as northEnd,p.project_area as projectArea,p.management_phone as managementPhone,p.building_land as buildingLand,p.green_area as greenArea,p.municipal as municipal,p.other_area as otherArea,p.all_area as allArea,p.get_type as getType,p.takeover_time as takeoverTime,p.state as state,p.is_old as isOld,p.is_indemnificatory as isIndemnificatory,p.air_scape as airScape,p.layout_plan as layoutPlan,p.service_contract as serviceContract  ");
        sql.append(" from company_project p,company_base_info c  where ");
        sql.append("  p.state=1");
        sql.append(" and p.company_id=c.id");

        commonSql(sql, area, qyName, proName);

        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());

        return sql.toString();
    }

    /**
     * 查询企业项目信息 条数
     */
    public String findByConditionCount(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String area = (String) map.get("area");
        String qyName = (String) map.get("qyName");
        String proName = (String) map.get("proName");

        sql.append("select count(*) ");
        sql.append(" from company_project p,company_base_info c ");
        sql.append(" where p.state=1");
        sql.append(" and p.company_id=c.id");

        commonSql(sql, area, qyName, proName);

        return sql.toString();
    }

    public void commonSql(StringBuffer sql, String area, String qyName, String proName) {
        if (StringUtils.isNotBlank(area)) {
            sql.append(" and (CASE p.community WHEN 0 THEN p.street in (").append(area).append(") ELSE p.community in (").append(area).append(") end)");
        }
        if (StringUtils.isNotBlank(qyName)) {
            sql.append(" and c.`name` like CONCAT('%','").append(qyName).append("','%')");
        }
        if (StringUtils.isNotBlank(proName)) {
            sql.append(" and p.project_name like CONCAT('%','").append(proName).append("','%')");
        }
    }

    /**
     * 查询企业项目下的人员信息
     */
    public String findProjectPerson(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        Page page = (Page) map.get("page");
        String name = (String) map.get("name");
        String depId = (String) map.get("depId");
        Integer proId = (Integer) map.get("proId");

        sql.append("select @rowno:=@rowno+1 row,p.id as id,DATE_FORMAT(p.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(p.gmt_modified, '%Y-%m-%d') as gmtModified,p.id as id,p.gmt_create as gmtCreate,p.gmt_modified as gmtModified,p.person_id as personId,p.project_id as projectId,p.company_id as companyId,p.department_id as departmentIdCode,(SELECT name FROM dic_info WHERE code = p.department_id) as departmentId,p.`name` as `name`,p.sex as sex,p.country_code as countryCode,p.birthday as birthday,p.education_level as educationLevelCode,(SELECT name FROM dic_info WHERE code = p.education_level) AS educationLevel,p.id_card as idCard,p.photo as photo,p.political_status as politicalStatus,p.phone as phone,p.major AS majorCode,(SELECT name FROM dic_info WHERE code = p.major) AS major,p.insurance as insurance,p.duties AS dutiesCode,(SELECT name FROM dic_info WHERE code = p.duties) AS duties,p.state as state,p.certificate as certificate,p.id_card_file_id as idCardFileId,p.certificate_file_id as certificateFileId,p.social_security_file_id as socialSecurityFileId,p.labor_contract_file_id as laborContractFileId,p.diploma_file_id as diplomaFileId  ");
        sql.append(" FROM company_project pro,company_person p ");
        sql.append(" where pro.id=p.project_id");
        sql.append(" and pro.state in (1,2,3)");
        sql.append(" and pro.project_id=").append(proId);
        //只显示在职人员
        sql.append(" and p.state=1 ");
        if (StringUtils.isNotBlank(name)) {
            sql.append(" and p.`name` like CONCAT('%','").append(name).append("','%')");
        }
        if (StringUtils.isNotBlank(depId)) {
            sql.append(" and p.department_id=").append(depId);
        }

        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 查询企业项目下的人员信息 条数
     */
    public String findProjectPersonCount(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String name = (String) map.get("name");
        String depId = (String) map.get("depId");
        Integer proId = (Integer) map.get("proId");

        sql.append("select count(*) from");
        sql.append(" company_project pro,company_person p");
        sql.append(" where pro.id=p.project_id");
        sql.append(" and pro.state in (1,2,3)");
        sql.append(" and pro.project_id=").append(proId);
        //只显示在职人员
        sql.append(" and p.state=1 ");
        if (StringUtils.isNotBlank(name)) {
            sql.append(" and p.`name` like CONCAT('%','").append(name).append("','%')");
        }
        if (StringUtils.isNotBlank(depId)) {
            sql.append(" and p.department_id=").append(depId);
        }

        return sql.toString();
    }

    /**
     * 获取该管理账号下的企业下的项目的物业用房信息
     */
    public String findProjectWYYF(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        Page page = (Page) map.get("page");
        String address = (String) map.get("address");
        Integer nature = (Integer) map.get("nature");
        Integer proId = (Integer) map.get("proId");

        sql.append("select @rowno:=@rowno+1 row,h.id as id ,DATE_FORMAT(h.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(h.gmt_modified, '%Y-%m-%d') as gmtModified,h.project_id as projectId,h.room_address as roomAddress,h.room_nature as roomNature,h.room_area as roomArea,h.room_state as roomState,h.remark as remark ");
        sql.append(" from company_project pro, company_housing h ");
        sql.append(" where pro.state in(1,2,3)");
        sql.append(" and pro.project_id = h.project_id");
        sql.append(" and pro.project_id=").append(proId);

        if (StringUtils.isNotBlank(address)) {
            sql.append(" and p.room_address like CONCAT('%','").append(address).append("','%'");
        }
        if (nature != null) {
            sql.append(" and p.room_nature=").append(nature);
        }
        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 获取该管理账号下的企业下的项目的物业用房信息 条数
     */
    public String findProjectWYYFCount(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String address = (String) map.get("address");
        Integer nature = (Integer) map.get("nature");
        Integer proId = (Integer) map.get("proId");

        sql.append("select count(*) from ");
        sql.append("company_project pro, company_housing h");
        sql.append(" where pro.state in(1,2,3)");
        sql.append(" and pro.project_id = h.project_id");
        sql.append(" and pro.project_id=").append(proId);

        if (StringUtils.isNotBlank(address)) {
            sql.append(" and p.room_address like CONCAT('%','").append(address).append("','%'");
        }
        if (nature != null) {
            sql.append(" and p.room_nature=").append(nature);
        }
        return sql.toString();
    }

    /**
     * 获取该账号管理区域下的项目下的委员会信息
     */
    public String findProjectCommittee(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        Page page = (Page) map.get("page");
        String name = (String) map.get("name");
        Integer sex = (Integer) map.get("sex");
        Integer proId = (Integer) map.get("proId");
        Integer isDirector = (Integer) map.get("isDirector");

        sql.append("select @rowno:=@rowno+1 row,cp.id as id,DATE_FORMAT(cp.gmt_create, '%Y-%m-%d') as gmtCreate,DATE_FORMAT(cp.gmt_modified, '%Y-%m-%d') as gmtModified,cp.project_id as projectId,cp.`name` as `name`,cp.sex as sex,cp.age as age,cp.education_level as educationLevelCode,(SELECT name FROM dic_info WHERE code = cp.education_level) AS educationLevel,cp.company as company,cp.phone as phone,cp.business_address as businessAddress,cp.address as address,cp.duty AS dutyCode,(SELECT name FROM dic_info WHERE code = cp.duty) AS duty,cp.political_status as politicalStatus,cp.state as state,cp.housing_area as housingArea,cp.remark as remark ");
        sql.append(" from company_proprietor cp,company_project pro  ");
        sql.append(" where pro.id=cp.project_id");
        sql.append(" and pro.project_id=").append(proId);

        if (StringUtils.isNotBlank(name)) {
            sql.append(" and p.`name` like CONCAT('%','").append(name).append("','%')");
        }
        if (sex != null) {
            sql.append(" and p.sex=").append(sex);
        }
        if (isDirector != null) {
            sql.append(" and pc.is_director=").append(isDirector);
        }

        sql.append(" LIMIT ");
        sql.append(page.getBeginIndex()).append(",").append(page.getLimit());
        return sql.toString();
    }

    /**
     * 获取该账号管理区域下的项目下的委员会信息 条数
     */
    public String findProjectCommitteeCount(HashMap<String, Object> map) {
        StringBuffer sql = new StringBuffer(20);
        String name = (String) map.get("name");
        Integer sex = (Integer) map.get("sex");
        Integer proId = (Integer) map.get("proId");
        Integer isDirector = (Integer) map.get("isDirector");

        sql.append("select count(*) ");
        sql.append(" from company_proprietor cp,company_project pro ");
        sql.append(" where pro.id=cp.project_id");
        sql.append(" and pro.project_id=").append(proId);

        if (StringUtils.isNotBlank(name)) {
            sql.append(" and p.`name` like CONCAT('%','").append(name).append("','%')");
        }
        if (sex != null) {
            sql.append(" and p.sex=").append(sex);
        }
        if (isDirector != null) {
            sql.append(" and pc.is_director=").append(isDirector);
        }

        return sql.toString();
    }

}
